﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class BolDetailDA : IBolDetailDA
    {
        private string ACTUAL_EQUIPMENT = "@ACTUAL_EQUIPMENT";
        private string ACTUAL_EQUIPMENT_TYPE = "@ACTUAL_EQUIPMENT_TYPE";
        private string BOL_ID = "@BOL_ID";
        private string CARRIER = "@CARRIER";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string DELIVERY_ID = "@DELIVERY_ID";
        private string DELIVERY_LINE_ID = "@DELIVERY_LINE_ID";
        private string DISPATCH_ID = "@DISPATCH_ID";
        private string DISPATCHED_BY = "@DISPATCHED_BY";
        private string DRIVER = "@DRIVER";
        private string GROSS_WEIGHT = "@GROSS_WEIGHT";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LINE_ID = "@LINE_ID";
        private string NET_WEIGHT = "@NET_WEIGHT";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PLAN_EQUIPMENT = "@PLAN_EQUIPMENT";
        private string PLAN_EQUIPMENT_TYPE = "@PLAN_EQUIPMENT_TYPE";
        private string QTY = "@QTY";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_BOL_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@BOL_DETAIL WHERE BOL_ID = @BOL_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_BOL_DETAIL = " DELETE FROM @_@BOL_DETAIL WHERE BOL_ID = @BOL_ID ";
        private string SQL_INSERT_BOL_DETAIL = " INSERT INTO @_@BOL_DETAIL ( WH_ID, BOL_ID, LINE_ID, DELIVERY_ID, DELIVERY_LINE_ID, OWNER_ID, SKU_ID, DISPATCH_ID, PLAN_EQUIPMENT, ACTUAL_EQUIPMENT, PLAN_EQUIPMENT_TYPE, ACTUAL_EQUIPMENT_TYPE, STATUS, DRIVER, CARRIER, DISPATCHED_BY, TRANSPORT_MODE, TRANSPORT_TYPE, QTY, PACK_ID, GROSS_WEIGHT, NET_WEIGHT, CUBE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @BOL_ID, @LINE_ID, @DELIVERY_ID, @DELIVERY_LINE_ID, @OWNER_ID, @SKU_ID, @DISPATCH_ID, @PLAN_EQUIPMENT, @ACTUAL_EQUIPMENT, @PLAN_EQUIPMENT_TYPE, @ACTUAL_EQUIPMENT_TYPE, @STATUS, @DRIVER, @CARRIER, @DISPATCHED_BY, @TRANSPORT_MODE, @TRANSPORT_TYPE, @QTY, @PACK_ID, @GROSS_WEIGHT, @NET_WEIGHT, @CUBE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_BOL_DETAIL = " SELECT  ROWID, WH_ID, BOL_ID, LINE_ID, DELIVERY_ID, DELIVERY_LINE_ID, OWNER_ID, SKU_ID, DISPATCH_ID, PLAN_EQUIPMENT, ACTUAL_EQUIPMENT, PLAN_EQUIPMENT_TYPE, ACTUAL_EQUIPMENT_TYPE, STATUS, DRIVER, CARRIER, DISPATCHED_BY, TRANSPORT_MODE, TRANSPORT_TYPE, QTY, PACK_ID, GROSS_WEIGHT, NET_WEIGHT, CUBE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@BOL_DETAIL ";
        private string SQL_UPDATE_BOL_DETAIL = " UPDATE @_@BOL_DETAIL SET WH_ID = @WH_ID, DELIVERY_ID = @DELIVERY_ID, DELIVERY_LINE_ID = @DELIVERY_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, DISPATCH_ID = @DISPATCH_ID, PLAN_EQUIPMENT = @PLAN_EQUIPMENT, ACTUAL_EQUIPMENT = @ACTUAL_EQUIPMENT, PLAN_EQUIPMENT_TYPE = @PLAN_EQUIPMENT_TYPE, ACTUAL_EQUIPMENT_TYPE = @ACTUAL_EQUIPMENT_TYPE, STATUS = @STATUS, DRIVER = @DRIVER, CARRIER = @CARRIER, DISPATCHED_BY = @DISPATCHED_BY, TRANSPORT_MODE = @TRANSPORT_MODE, TRANSPORT_TYPE = @TRANSPORT_TYPE, QTY = @QTY, PACK_ID = @PACK_ID, GROSS_WEIGHT = @GROSS_WEIGHT, NET_WEIGHT = @NET_WEIGHT, CUBE = @CUBE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE BOL_ID = @BOL_ID AND LINE_ID = @LINE_ID  ";
        private string STATUS = "@STATUS";
        private string TRANSPORT_MODE = "@TRANSPORT_MODE";
        private string TRANSPORT_TYPE = "@TRANSPORT_TYPE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckBolDetailIDUnique(string bolDetailID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolDetailID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_BOL_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckBolDetailIDUnique(DataBase dataBase, DbTransaction tran, string bolDetailID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolDetailID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_BOL_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteBolDetail(List<string> bolDetailIDs, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < bolDetailIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolDetailIDs[i]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BOL_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteBolDetail(List<string> bolDetailIDs, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < bolDetailIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolDetailIDs[i]), new SqlParameter("@LINE_ID", lineIDs[i]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BOL_DETAIL.Replace("@_@", whLoginID[i]) + "AND LINE_ID = @LINE_ID ", paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteBolDetail(DataBase dataBase, DbTransaction tran, List<string> bolDetailIDs, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < bolDetailIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolDetailIDs[i]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BOL_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteBolDetail(DataBase dataBase, DbTransaction tran, List<string> bolDetailIDs, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < bolDetailIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolDetailIDs[i]), new SqlParameter("@LINE_ID", lineIDs[i]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BOL_DETAIL.Replace("@_@", whLoginID[i]) + "AND LINE_ID = @LINE_ID ", paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteBolDetailByDispatchID(DataBase dataBase, DbTransaction tran, string dispatchID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = "DELETE FROM @_@BOL_DETAIL WHERE DISPATCH_ID = '" + dispatchID + "'";
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID));
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllBolDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllBolDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public BolDetailInfo GetBolDetailByID(string boldetailID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID) + " WHERE BOL_ID = @BOL_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", boldetailID), new SqlParameter("@LINE_ID", lineID) };
            BolDetailInfo bolDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    bolDetailInfo = new BolDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BOL_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DISPATCH_ID"].ToString(), reader["PLAN_EQUIPMENT"].ToString(), reader["ACTUAL_EQUIPMENT"].ToString(), reader["PLAN_EQUIPMENT_TYPE"].ToString(), reader["ACTUAL_EQUIPMENT_TYPE"].ToString(), reader["STATUS"].ToString(), reader["DRIVER"].ToString(), reader["CARRIER"].ToString(), reader["DISPATCHED_BY"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["TRANSPORT_TYPE"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["PACK_ID"].ToString(), (reader["GROSS_WEIGHT"].ToString() != "") ? decimal.Parse(reader["GROSS_WEIGHT"].ToString()) : 0M, (reader["NET_WEIGHT"].ToString() != "") ? decimal.Parse(reader["NET_WEIGHT"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return bolDetailInfo;
        }

        public BolDetailInfo GetBolDetailByID(DataBase dataBase, DbTransaction tran, string boldetailID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID) + " WHERE BOL_ID = @BOL_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", boldetailID), new SqlParameter("@LINE_ID", lineID) };
            BolDetailInfo bolDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                bolDetailInfo = new BolDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BOL_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DISPATCH_ID"].ToString(), reader["PLAN_EQUIPMENT"].ToString(), reader["ACTUAL_EQUIPMENT"].ToString(), reader["PLAN_EQUIPMENT_TYPE"].ToString(), reader["ACTUAL_EQUIPMENT_TYPE"].ToString(), reader["STATUS"].ToString(), reader["DRIVER"].ToString(), reader["CARRIER"].ToString(), reader["DISPATCHED_BY"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["TRANSPORT_TYPE"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["PACK_ID"].ToString(), (reader["GROSS_WEIGHT"].ToString() != "") ? decimal.Parse(reader["GROSS_WEIGHT"].ToString()) : 0M, (reader["NET_WEIGHT"].ToString() != "") ? decimal.Parse(reader["NET_WEIGHT"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return bolDetailInfo;
        }

        public DataSet GetBolDetailByQueryList(List<string> sqlWhere, BolDetailQueryEntity boldetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!boldetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, boldetailQuery.CurrentPage, boldetailQuery.PageSize, boldetailQuery.SortField, boldetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetBolDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, BolDetailQueryEntity boldetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, boldetailQuery.CurrentPage, boldetailQuery.PageSize, boldetailQuery.SortField, boldetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<BolDetailInfo> GetBolDetailListByID(string boldetailID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID) + " WHERE BOL_ID = @BOL_ID   ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", boldetailID) };
            List<BolDetailInfo> bolDetailInfos = new List<BolDetailInfo>();
            BolDetailInfo bolDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    bolDetailInfo = new BolDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BOL_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DISPATCH_ID"].ToString(), reader["PLAN_EQUIPMENT"].ToString(), reader["ACTUAL_EQUIPMENT"].ToString(), reader["PLAN_EQUIPMENT_TYPE"].ToString(), reader["ACTUAL_EQUIPMENT_TYPE"].ToString(), reader["STATUS"].ToString(), reader["DRIVER"].ToString(), reader["CARRIER"].ToString(), reader["DISPATCHED_BY"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["TRANSPORT_TYPE"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["PACK_ID"].ToString(), (reader["GROSS_WEIGHT"].ToString() != "") ? decimal.Parse(reader["GROSS_WEIGHT"].ToString()) : 0M, (reader["NET_WEIGHT"].ToString() != "") ? decimal.Parse(reader["NET_WEIGHT"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    bolDetailInfos.Add(bolDetailInfo);
                }
            }
            return bolDetailInfos;
        }

        public List<BolDetailInfo> GetBolDetailListByID(DataBase dataBase, DbTransaction tran, string boldetailID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL_DETAIL.Replace("@_@", whLoginID) + " WHERE BOL_ID = @BOL_ID   ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", boldetailID) };
            List<BolDetailInfo> bolDetailInfos = new List<BolDetailInfo>();
            BolDetailInfo bolDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                bolDetailInfo = new BolDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BOL_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DISPATCH_ID"].ToString(), reader["PLAN_EQUIPMENT"].ToString(), reader["ACTUAL_EQUIPMENT"].ToString(), reader["PLAN_EQUIPMENT_TYPE"].ToString(), reader["ACTUAL_EQUIPMENT_TYPE"].ToString(), reader["STATUS"].ToString(), reader["DRIVER"].ToString(), reader["CARRIER"].ToString(), reader["DISPATCHED_BY"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["TRANSPORT_TYPE"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["PACK_ID"].ToString(), (reader["GROSS_WEIGHT"].ToString() != "") ? decimal.Parse(reader["GROSS_WEIGHT"].ToString()) : 0M, (reader["NET_WEIGHT"].ToString() != "") ? decimal.Parse(reader["NET_WEIGHT"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                bolDetailInfos.Add(bolDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return bolDetailInfos;
        }

        public int InsertBolDetail(BolDetailInfo bolDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BolDetail_Parameters(bolDetailInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_BOL_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertBolDetail(DataBase dataBase, DbTransaction tran, BolDetailInfo bolDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BolDetail_Parameters(bolDetailInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_BOL_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_BolDetail_Parameters(BolDetailInfo bolDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.BOL_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.DELIVERY_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DISPATCH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.PLAN_EQUIPMENT, SqlDbType.VarChar, 30), new SqlParameter(this.ACTUAL_EQUIPMENT, SqlDbType.VarChar, 30), new SqlParameter(this.PLAN_EQUIPMENT_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.ACTUAL_EQUIPMENT_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.STATUS, SqlDbType.VarChar, 30), new SqlParameter(this.DRIVER, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER, SqlDbType.VarChar, 30), new SqlParameter(this.DISPATCHED_BY, SqlDbType.VarChar, 30), 
                new SqlParameter(this.TRANSPORT_MODE, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSPORT_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 30), new SqlParameter(this.GROSS_WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.NET_WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30)
             };
            if (!string.IsNullOrEmpty(bolDetailInfo.WhID))
            {
                paramArray[0].Value = bolDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = bolDetailInfo.BolID;
            paramArray[2].Value = bolDetailInfo.LineID;
            paramArray[3].Value = bolDetailInfo.DeliveryID;
            paramArray[4].Value = bolDetailInfo.DeliveryLineID;
            paramArray[5].Value = bolDetailInfo.OwnerID;
            paramArray[6].Value = bolDetailInfo.SkuID;
            paramArray[7].Value = bolDetailInfo.DispatchID;
            paramArray[8].Value = bolDetailInfo.PlanEquipment;
            paramArray[9].Value = bolDetailInfo.ActualEquipment;
            paramArray[10].Value = bolDetailInfo.PlanEquipmentType;
            paramArray[11].Value = bolDetailInfo.ActualEquipmentType;
            paramArray[12].Value = bolDetailInfo.Status;
            paramArray[13].Value = bolDetailInfo.Driver;
            paramArray[14].Value = bolDetailInfo.Carrier;
            paramArray[15].Value = bolDetailInfo.DispatchedBy;
            paramArray[0x10].Value = bolDetailInfo.TransportMode;
            paramArray[0x11].Value = bolDetailInfo.TransportType;
            paramArray[0x12].Value = bolDetailInfo.Qty;
            paramArray[0x13].Value = bolDetailInfo.PackID;
            paramArray[20].Value = bolDetailInfo.GrossWeight;
            paramArray[0x15].Value = bolDetailInfo.NetWeight;
            paramArray[0x16].Value = bolDetailInfo.Cube;
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf1))
            {
                paramArray[0x17].Value = bolDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf2))
            {
                paramArray[0x18].Value = bolDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf3))
            {
                paramArray[0x19].Value = bolDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf4))
            {
                paramArray[0x1a].Value = bolDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf5))
            {
                paramArray[0x1b].Value = bolDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf6))
            {
                paramArray[0x1c].Value = bolDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf7))
            {
                paramArray[0x1d].Value = bolDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf8))
            {
                paramArray[30].Value = bolDetailInfo.Udf8;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf9))
            {
                paramArray[0x1f].Value = bolDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Udf10))
            {
                paramArray[0x20].Value = bolDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolDetailInfo.Remark))
            {
                paramArray[0x21].Value = bolDetailInfo.Remark;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            paramArray[0x22].Value = bolDetailInfo.IsActive;
            paramArray[0x23].Value = bolDetailInfo.CreatedBy;
            paramArray[0x24].Value = bolDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateBolDetail(BolDetailInfo bolDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BolDetail_Parameters(bolDetailInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_BOL_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateBolDetail(DataBase dataBase, DbTransaction tran, BolDetailInfo bolDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BolDetail_Parameters(bolDetailInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_BOL_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

